﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class LweekplanDAL:ILweekplanDAL
    {
        public int AddLweekplan(LweekplanInfo lwp)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, lwp.Num),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, lwp.Ku),
                DALHelper.MakeInParam(":sday", OracleType.VarChar, lwp.Sday),
                DALHelper.MakeInParam(":eday", OracleType.VarChar, lwp.Eday),
                DALHelper.MakeInParam(":wcl", OracleType.Number, lwp.Wcl),
                DALHelper.MakeInParam(":xday", OracleType.VarChar, lwp.Xday)
            };
            return (DALHelper.RunCommand(@"insert into dd.lweekplan (num, ku, sday, eday, wcl, xday) values (:num, :ku, to_date(:sday,'yyyy-mm-dd'), to_date(:eday,'yyyy-mm-dd'), :wcl, to_date(:xday,'yyyy-mm-dd'))", prams));
        }
        public int UpdateLweekplan(string num, string ku, string sday, string eday, decimal wcl, decimal jk)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":sday", OracleType.VarChar, sday),
                DALHelper.MakeInParam(":eday", OracleType.VarChar, eday),
                DALHelper.MakeInParam(":wcl", OracleType.Number, wcl),
                DALHelper.MakeInParam(":jk", OracleType.Number, jk)

            };
            return (DALHelper.RunCommand(@"update dd.lweekplan set ku = :ku, sday = to_date(:sday,'yyyy-mm-dd'), eday = to_date(:eday,'yyyy-mm-dd'), wcl = :wcl, jk = :jk where num = :num", prams));
        }
        public int UpdateLweekplanJK(string num, decimal jk)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                
                DALHelper.MakeInParam(":jk", OracleType.Number, jk)

            };
            return (DALHelper.RunCommand(@"update dd.lweekplan set jk = :jk where num = :num", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.lweekplan where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.lweekplan where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectByDay(string sday)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":sday", OracleType.VarChar, sday)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.lweekplan where xday = to_date(:sday,'yyyy-mm-dd')", prams, "Sbyday"));
        }
        public decimal ltr(string cday, string ku)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":cday", OracleType.VarChar, cday),
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select (wcl+jk)/7 from dd.lweekplan where ku = :ku and eday >= to_date(:cday,'yyyy-mm-dd') and sday <= to_date(:cday,'yyyy-mm-dd')", prams);
            return Convert.ToDecimal(obj.ToString());
        }
        public string AUTOID()
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            num = "lwp" + time.ToString();


            return num;
        }
    }
}
